package com.cn.dim.utils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.MultipartResolver;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import com.cn.dim.pojo.User;

public class ExcelUtil 
{
	public static void createExcelChkCount(HttpServletRequest request, HttpServletResponse response,
			List<Map<String,Object>> list, String fileName, List<String> title)
	{
		try {
			// 创建Excel的工作书册 Workbook,对应到一个excel文档
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFCellStyle style = workbook.createCellStyle();
			// 生成一个字体
			HSSFFont font = workbook.createFont();
			// 字体增粗
			//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short) 11);
			style.setFont(font);
			// 创建Excel的工作sheet,对应到一个excel文档的tab
			HSSFSheet sheet = workbook.createSheet("sheet1");
			// 创建Excel的sheet的一行 (表头)
			HSSFRow row = sheet.createRow(0);
			// 表头内容填充
			for (int i = 0; i < title.size(); i++) {
				// 设置excel每列宽度
				sheet.setColumnWidth(i, 5000);
				HSSFCell cell = row.createCell(i);
				//cell.setCellValue(title.get(i));
				cell.setCellValue(new HSSFRichTextString(title.get(i)));
				cell.setCellStyle(style);
			}
			// 创建内容行
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setWrapText(true);// 自动换行
			//cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));//保留两位小数格式
			for (int j = 0; j < list.size(); j++) {
				HSSFRow contentRow = sheet.createRow(j + 1);
				for (int k = 0; k < title.size(); k++) {
					HSSFCell cell = contentRow.createCell(k);
					switch (k) {
					case 0://编号
						Map<String,Object> m0=list.get(j);
						String s0=String.valueOf(m0.get("rowDataNum"));
						if (s0!= null) {// 每一列的名称
							cell.setCellValue(s0);
						}
						break;
					case 1://巡查地点
						Map<String,Object> m1=list.get(j);
						String s1=String.valueOf(m1.get("chkpointname"));
						if (s1 != null) {//每一列的名称
							cell.setCellValue(s1 );
						}
						break;
					case 2://规定巡查次数
						Map<String,Object> m2=list.get(j);
						String s2=String.valueOf(m2.get("allDefNum"));
						if (s2 != null) {//每一列的名称
							cell.setCellValue(s2);						
						}
						break;
					case 3://实际巡查次数
						Map<String,Object> m3=list.get(j);
						String s3=String.valueOf(m3.get("realNum"));
						if (s3!= null) {// 每一列的名称
							cell.setCellValue(s3);
						}
						break;
					case 4://正常次数
						Map<String,Object> m4=list.get(j);
						String s4=String.valueOf(m4.get("okNum"));
						if (s4!= null) {// 每一列的名称
							cell.setCellValue(s4);
						}
						break;
					case 5://异常次数
						Map<String,Object> m5=list.get(j);
						String s5=String.valueOf(m5.get("errNum"));
						if (s5!= null) {// 每一列的名称
							cell.setCellValue(s5);
						}
						break;
					default:
						break;
					}
					cell.setCellStyle(cellStyle);
				}
			}
 
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			try {
				workbook.write(os);
			} catch (IOException e) {
				e.printStackTrace();
			}
			byte[] content = os.toByteArray();
			InputStream is = new ByteArrayInputStream(content);
			// 设置response参数，可以打开下载页面
			response.reset();
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes("gb2312"), "iso-8859-1"));
			ServletOutputStream out = response.getOutputStream();
			BufferedInputStream bis = null;
			BufferedOutputStream bos = null;
			try {
				bis = new BufferedInputStream(is);
				bos = new BufferedOutputStream(out);
				byte[] buff = new byte[2048];
				int bytesRead;
				// Simple read/write loop.
				while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
					bos.write(buff, 0, bytesRead);
				}
			} catch (final IOException e) {
				throw e;
			} finally {
				if (bis != null)
					bis.close();
				if (bos != null)
					bos.close();
			}
		} catch (Exception e) {
				e.printStackTrace();
		}
	}
	
	public static void createExcelChkTask(HttpServletRequest request, HttpServletResponse response,
			List<Map<String,Object>> list, String fileName, List<Map<String,Object>> title_colName,String title_topic)
	{
		String[] title_col_keys=new String[title_colName.size()];
		String[] title_col_names=new String[title_colName.size()];
		//按照excel的顺序取列名称与列的key值
		for(int i=0;i<title_colName.size();i++)
		{
			Map<String,Object> map=title_colName.get(i);
			for(String key : map.keySet())
			{
				String name =(String)map.get(key);
				title_col_keys[i]=key;
				title_col_names[i]=name;
			}
		}
		
		try {
			// 创建Excel的工作书册 Workbook,对应到一个excel文档
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFCellStyle style = workbook.createCellStyle();
			// 生成一个字体
			HSSFFont font = workbook.createFont();
			// 字体增粗
			//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short) 11);
			style.setFont(font);
			// 创建Excel的工作sheet,对应到一个excel文档的tab
			//HSSFSheet sheet = workbook.createSheet("sheet1");
			HSSFSheet sheet = workbook.createSheet(title_topic);
			
			// 创建Excel的sheet的一行 (表头)
			HSSFRow row_title = sheet.createRow(0);
			HSSFCell cell_title = row_title.createCell(0); // 在第1行第1列位置创建一个单元格
			cell_title.setCellValue(title_topic); // 单元格填充内容
			
			HSSFCellStyle cellStyle_title = workbook.createCellStyle(); // 单元格样式
			Font fontStyle_title = workbook.createFont(); // 字体样式
			fontStyle_title.setBold(true); // 加粗
			fontStyle_title.setFontName("黑体"); // 字体
			fontStyle_title.setFontHeightInPoints((short) 11); // 大小
			// 将字体样式添加到单元格样式中 
			cellStyle_title.setFont(fontStyle_title);
			// 边框，居中
			cellStyle_title.setAlignment(HorizontalAlignment.CENTER);
			//cellStyle_title.setBorderBottom(BorderStyle.THIN);
			//cellStyle_title.setBorderLeft(BorderStyle.THIN);
			//cellStyle_title.setBorderRight(BorderStyle.THIN);
			//cellStyle_title.setBorderTop(BorderStyle.THIN);
			cell_title.setCellStyle(cellStyle_title); // 为单元格添加样式
			
			// 合并单元格
			CellRangeAddress cra =new CellRangeAddress(0, 0, 0, title_colName.size()-1); // 起始行, 终止行, 起始列, 终止列
			sheet.addMergedRegion(cra);
			
			//正式表格数据开始
			HSSFRow row = sheet.createRow(1);//正式内容从第2行开始
			// 表头内容填充
			for (int i = 0; i < title_col_names.length; i++) {
				// 设置excel每列宽度
				sheet.setColumnWidth(i, 5000);
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(title_col_names[i]);//取列名数组的每项值
				cell.setCellStyle(style);
			}
			// 创建内容行
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setWrapText(true);// 自动换行
			//cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));//保留两位小数格式
			for (int j = 0; j < list.size(); j++) {
				HSSFRow contentRow = sheet.createRow(j + 2);//数据行从第三行开始
				Map<String,Object> m=list.get(j);
				for (int k = 0; k < title_col_keys.length; k++) {
					HSSFCell cell = contentRow.createCell(k);
					String key=title_col_keys[k];
					String value=(String)m.get(key);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle);
				}
			}
 
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			try {
				workbook.write(os);
			} catch (IOException e) {
				e.printStackTrace();
			}
			byte[] content = os.toByteArray();
			InputStream is = new ByteArrayInputStream(content);
			// 设置response参数，可以打开下载页面
			response.reset();
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes("gb2312"), "iso-8859-1"));
			ServletOutputStream out = response.getOutputStream();
			BufferedInputStream bis = null;
			BufferedOutputStream bos = null;
			try {
				bis = new BufferedInputStream(is);
				bos = new BufferedOutputStream(out);
				byte[] buff = new byte[2048];
				int bytesRead;
				// Simple read/write loop.
				while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
					bos.write(buff, 0, bytesRead);
				}
			} catch (final IOException e) {
				throw e;
			} finally {
				if (bis != null)
					bis.close();
				if (bos != null)
					bos.close();
			}
		} catch (Exception e) {
				e.printStackTrace();
		}
	}

	public static void getDataListFromExcel(HttpServletRequest request,HttpServletResponse response,List<User> userList,List<Map<String,Object>> u_d_pList,List<Map<String,Object>> u_rList)
	{
		try
		{			 	      
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String cdate_s = sdf.format(new Date());//创建日期
			
			//PrintWriter out=response.getWriter();
        	MultipartResolver resolver = new CommonsMultipartResolver(request.getSession().getServletContext());
            MultipartHttpServletRequest multipartRequest = resolver.resolveMultipart(request);

            //输入流  
		    InputStream in =null;
		    MultipartFile file = multipartRequest.getFile("userFile"); 
		    String filename=file.getOriginalFilename();
		    in = file.getInputStream(); 
		    //得到excel  
		    Workbook wb = null;
		    String fileType = filename.substring(filename.lastIndexOf("."));  
		    if((".xls").equals(fileType))
		    {  
		        wb = new HSSFWorkbook(in);  //2003-  
		    }
		    if((".xlsx").equals(fileType))
		    {  
		        wb = new XSSFWorkbook(in);  //2007+  
		    }
		    //得到sheet  
		    Sheet sheet = wb.getSheetAt(0); //默认取第一个sheet 	         
		    int rowsNum = sheet.getLastRowNum();//  	        	          
		    for(int j=1; j<rowsNum+1;j++) //第一行为表头，所以从第二行开始
		    {// getLastRowNum，获取最后一行的行标
		        Row row =sheet.getRow(j);
		        if (row != null) 
		        {
		        	User user=new User();
		        	Map<String,Object> u_d_p=new HashMap<>();
		        	Map<String,Object> u_r=new HashMap<>();
		        	
		        	//String userid=row.getCell(0).toString();
		        	String userid=StringUtil.makeUUID();
		        	user.setId(userid);//用于数据库插入
		        	user.setEmpnum(row.getCell(1).toString());//第二列是员工号
		        	user.setName(row.getCell(2).toString());//第三列是姓名
		        	user.setUsername(row.getCell(1).toString()+row.getCell(3).toString());//第二列与第四列组合成用户名
		        	
		        	DecimalFormat df = new DecimalFormat("#");
		        	if(row.getCell(4)!=null)
		        	{
			        	String telephone=df.format(row.getCell(4).getNumericCellValue());
			        	user.setTelephone(telephone);//第5列是电话
		        	}
		        	user.setIsdel("0");//补充3个默认项
		        	user.setIsused("0");
		        	user.setCreatetime(cdate_s);
		        	userList.add(user);
		        	
		        	u_d_p.put("userid", userid);
		        	u_d_p.put("depid", "A0");
		        	u_d_p.put("posid", "A0");
		        	u_d_pList.add(u_d_p);
		        	
		        	u_r.put("userid", userid);
		        	u_r.put("roleid", "A0");
		        	u_rList.add(u_r);
		        }
		    }
		    wb.close();	  
		 } catch (IOException e) 
	     {  
		      e.printStackTrace();  
		 }

	    System.out.println(userList);
	    
	}
}
